package com.sz.biz.logistics.bill.service.impl;

import com.github.tobato.fastdfs.domain.StorePath;
import com.github.tobato.fastdfs.exception.FdfsServerException;
import com.github.tobato.fastdfs.proto.ErrorCodeConstants;
import com.github.tobato.fastdfs.service.FastFileStorageClient;
import com.google.common.collect.Lists;
import com.sz.biz.common.AbstractVersionEntityService;
import com.sz.biz.common.approval.Approval;
import com.sz.biz.common.approval.ApprovalService;
import com.sz.biz.common.constants.ModuleNames;
import com.sz.biz.common.customer.entity.CusUser;
import com.sz.biz.common.customer.service.CustomerUserService;
import com.sz.biz.logistics.bill.dto.*;
import com.sz.biz.logistics.bill.entity.*;
import com.sz.biz.logistics.bill.service.BillingOrderRelService;
import com.sz.biz.logistics.bill.service.BillingService;
import com.sz.biz.logistics.bill.service.BillingTaskService;
import com.sz.biz.logistics.constants.ErrorCodes;
import com.sz.biz.logistics.constants.QuotationConstants;
import com.sz.biz.logistics.ord.service.OrdServiceBillService;
import com.sz.biz.trade.constants.TrdConstants;
import com.sz.common.base.constants.UserActions;
import com.sz.common.base.dao.ParamData;
import com.sz.common.base.dto.CommonModel;
import com.sz.common.base.dto.QResultDto;
import com.sz.common.base.dto.QueryParams;
import com.sz.common.base.exception.ErrorCode;
import com.sz.common.base.exception.Exceptions;
import com.sz.common.base.fastdfs.FastDFSClientWrapper;
import com.sz.common.base.utils.DateUtils;
import com.sz.common.base.utils.EntityUtils;
import com.sz.common.base.utils.StringUtils;
import com.sz.common.core.service.PrincipalLogUtils;
import com.sz.common.core.service.PrincipalUtils;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.tomcat.util.http.fileupload.ByteArrayOutputStream;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import java.io.ByteArrayInputStream;
import java.math.BigDecimal;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Function: 账单服务<br>
 * Author: jifeixing <br>
 * Date: 2017-05-17 14:10:00
 */
@Service("billingService")
@Transactional(propagation = Propagation.REQUIRED, rollbackFor = Exception.class)
public class BillingServiceImpl extends AbstractVersionEntityService implements BillingService {

    private static final int MAX_SIZE = 65500;
    private static final int PERCENT_LENGTH = 5;
    private static final int AMOUNT_LENGTH = 10;
    @Autowired
    private BillingOrderRelService billingOrderRelService;
    @Autowired
    private ApprovalService approvalService;
    @Autowired
    private OrdServiceBillService ordServiceBillService;
    @Autowired
    private BillingTaskService billingTaskService;
    @Autowired
    private FastFileStorageClient storageClient;
    @Autowired
    private CustomerUserService customerUserService;

    @Autowired
    private FastDFSClientWrapper fastDFSClientWrapper;

    /**
     * 批量删除账单
     *
     * @param billNos
     */
    @Override
    public void batchdeleteByNos(List<String> billNos, String type, List<Integer> suppliers) {
        if (null == billNos || billNos.size() <= 0) {
            throw Exceptions.bizException(ErrorCodes.ERROR_ARGUMENT_ILLEGAL);
        }
        // 判断要删除的账单状态是否包含其它非草稿状态
        checkStatusPreDelete(billNos, BillingStatus.DRAFT, ErrorCodes.ERROR_FINANCIAL_DRAFT_CAN_DELETE);
        deleteBills(billNos, BillingStatus.DRAFT, type, suppliers);
    }

    /**
     * 批量删除账单信息-客户平台
     * 只能删除已付款的账单
     *
     * @param billNos
     */
    @Override
    public void batchdeleteByNosCustomer(List<String> billNos, String type, List<Integer> suppliers) {
        if (null == billNos || billNos.size() <= 0) {
            throw Exceptions.bizException(ErrorCodes.ERROR_ARGUMENT_ILLEGAL);
        }
        // 判断要删除的账单状态是否包含其它非草稿状态
        checkStatusPreDelete(billNos, BillingStatus.PAID, ErrorCodes.ERROR_FINANCIAL_PAID_CAN_DELETE);
        deleteBills(billNos, BillingStatus.PAID, type, suppliers);
    }

    /**
     * 删除账单
     *
     * @param billNos
     */
    private void deleteBills(List<String> billNos, BillingStatus status, String type, List<Integer> suppliers) {
        List<Integer> customers = new ArrayList<Integer>();
        if (billNos != null && billNos.size() > 0) {
            for (String billNo : billNos) {
                Billing billing = findByBillNo(billNo, type);
                if (billing.getCustomerId() != null) {
                    customers.add(billing.getCustomerId());
                }
            }
        }
        ParamData paramData = new ParamData();
        paramData.put("billNos", billNos);
        paramData.put("status", status);
        paramData.put("type", type);
        dao.delete(getSqlName("batchdeleteByNos"), paramData);
        // 删除账单下所有关联订单
        if (customers != null && customers.size() > 0) {
            for (int customer : customers) {
                billingOrderRelService.deleteByBillNos(billNos, type, customer);
            }
            billingOrderRelService.deleteByBillNosOrderRe(billNos, type);
        } else {
            billingOrderRelService.deleteByBillNosOrderRe(billNos, type);
        }

        //billingOrderRelService.deleteByBillNos(billNos,type);
    }

    /**
     * 只有草稿/已付款状态的可以删除
     *
     * @param billNos
     */
    private void checkStatusPreDelete(List<String> billNos, BillingStatus statusDelete, ErrorCode errorCode) {
        ParamData paramData = new ParamData();
        paramData.put("billNos", billNos);
        List<Billing> bills = dao.findForList(getSqlName("findActivedByBillNos"), paramData, Billing.class);
        if (null != bills && bills.size() > 0) {
            for (Billing bill : bills) {
                if (!bill.getStatus().getCode().equals(statusDelete.getCode())) {
                    throw Exceptions.bizException(errorCode);
                }
            }
        }
    }

    /**
     * 根据任务编码删除账单
     *
     * @param taskCodes
     */
    @Override
    public void batchDeleteByTaskCodes(List<String> taskCodes, String type) {
        ParamData paramData = new ParamData();
        if (taskCodes != null && taskCodes.size() > 0) {
            paramData.put("taskCodes", taskCodes);
        }
        paramData.put("type", type);
        dao.delete(getSqlName("batchDeleteByTaskCodes"), paramData);
    }

    /**
     * 客户平台账单刘流转，已出帐，已付款，逾期未结
     *
     * @param billNo
     * @return
     */
    @Override
    public List<Map<String, String>> findFinancialApprovals(String billNo) {
        List<String> billingStatus = new ArrayList<>();
        billingStatus.add(BillingStatus.CHARGEOFF.getCode());
        billingStatus.add(BillingStatus.PAID.getCode());
        billingStatus.add(BillingStatus.OVERDUE.getCode());
        List<Map<String, String>> approvals = new ArrayList<>();
        List<Approval> approvalList = approvalService.getApprovalsForBill(ApprovalService.ENTITY_TYPE_FINANCIAL, billNo, billingStatus);
        if (null != approvalList && approvalList.size() > 0) {
            for (Approval approval : approvalList) {
                Map<String, String> map = new HashMap<>();
                map.put("billNo", approval.getOwnerId());
                map.put("status", BillingStatus.valueOfEnum(approval.getToStatus() + "").getName());
                map.put("time", DateUtils.formatDetailDate(approval.getApprovalTime()));
                approvals.add(map);
            }
        }
        return approvals;
    }

    /**
     * 已付款
     *
     * @param billNos
     */
    @Override
    public void paid(List<String> billNos, String type) {
        if (CollectionUtils.isEmpty(billNos)) {
            return;
        }
        // 修改账单状态
        updateBillingStatus(billNos, BillingStatus.CHARGEOFF, BillingStatus.PAID, "", "账单已付款", null, type);
    }

    /**
     * 导出账单
     *
     * @param billNos
     */
    @Override
    public List<BillingUrlDto> export(List<String> billNos, String type) {
        List<BillingUrlDto> list = new ArrayList<BillingUrlDto>();
        //只有已出帐账单可以导出
        checkExportBills(billNos);
        ParamData pd = new ParamData();
        pd.put("billNos", billNos);
        pd.put("type", type);
        list = dao.findForList(getSqlName("findURLByBillNos"), pd, BillingUrlDto.class);
        //容错
        if (!CollectionUtils.isEmpty(list)) {
            list.forEach(billing -> {
                boolean isRefetch = false;
                if (!StringUtils.isEmpty(billing.getUrl())) {
                    try {
                        fastDFSClientWrapper.getFileMetaInfo(billing.getUrl());
                    } catch (FdfsServerException exception) {
                        if (exception.getErrorCode() == ErrorCodeConstants.ERR_NO_ENOENT) {
                            exportBills(Collections.singletonList(billing.getBillNo()), type);
                            isRefetch = true;
                        }
                    }
                } else {
                    exportBills(Collections.singletonList(billing.getBillNo()), type);
                    isRefetch = true;
                }
                if (isRefetch) {
                    ParamData pdRe = new ParamData();
                    pdRe.put("billNos", Collections.singletonList(billing.getBillNo()));
                    List<BillingUrlDto> listRe = dao.findForList(getSqlName("findURLByBillNos"), pdRe, BillingUrlDto.class);
                    if (!CollectionUtils.isEmpty(listRe)) {
                        billing.setUrl(listRe.get(0).getUrl());
                    }
                }
            });
        }
        return list;

    }

    /**
     * 导出账单-客户平台
     *
     * @param billNos
     */
    @Override
    public List<BillingUrlDto> exportCustomer(List<String> billNos, String type) {
        //只有已出帐账单可以导出
        checkExportBills(billNos);
        //是否主帐号判断
        Integer userId = PrincipalUtils.getAccountId();
        if (null != userId) {
            //帐号信息
            CusUser primaryAccount = isPrimaryAccount(userId);
            if (null != primaryAccount && primaryAccount.getIsSystem()) {
                ParamData pd = new ParamData();
                pd.put("billNos", billNos);
                pd.put("customerId", primaryAccount.getRefCustomerId());
                List<BillingUrlDto> list = dao.findForList(getSqlName("findURLByBillNos"), pd, BillingUrlDto.class);
                //容错
                if (!CollectionUtils.isEmpty(list)) {
                    list.forEach(billing -> {
                        boolean isRefetch = false;
                        if (!StringUtils.isEmpty(billing.getUrl())) {
                            try {
                                fastDFSClientWrapper.getFileMetaInfo(billing.getUrl());
                            } catch (FdfsServerException exception) {
                                if (exception.getErrorCode() == ErrorCodeConstants.ERR_NO_ENOENT) {
                                    exportBills(Collections.singletonList(billing.getBillNo()), type);
                                    isRefetch = true;
                                }
                            }
                        } else {
                            exportBills(Collections.singletonList(billing.getBillNo()), type);
                            isRefetch = true;
                        }
                        if (isRefetch) {
                            ParamData pdRe = new ParamData();
                            pdRe.put("billNos", Collections.singletonList(billing.getBillNo()));
                            pdRe.put("customerId", primaryAccount.getRefCustomerId());
                            List<BillingUrlDto> listRe = dao.findForList(getSqlName("findURLByBillNos"), pdRe, BillingUrlDto.class);
                            if (!CollectionUtils.isEmpty(listRe)) {
                                billing.setUrl(listRe.get(0).getUrl());
                            }
                        }
                    });
                }
                return list;
            } else {
                //只有主帐号可以导出账单
                throw Exceptions.bizException(ErrorCodes.ERROR_FINANCIAL_PRIMARY_ACCOUNT_CAN_EXPORT);
            }
        }
        return null;
    }

    private void exportBills(List<String> billNos, String type) {
        if (TrdConstants.BillType.PAYABLE.equals(type)) {
            List<BillingAndOrderListDto> list = new ArrayList<BillingAndOrderListDto>();
            if (billNos != null && billNos.size() > 0) {
                for (String billNo : billNos) {
                    BillingListDto billingListDto = findBillingInfo(billNo, type);
                    BillingAndOrderListDto billingAndOrderListDto = new BillingAndOrderListDto();
                    EntityUtils.copyPropertiesIgnoreNull(billingListDto, billingAndOrderListDto);
                    // 账单下所有订单
                    List<BillingOrderListStringDto> billingOrderListDtoList = billingOrderRelService.findOrdersByBillNo(billingListDto.getBillNo(), type);
                    billingAndOrderListDto.setOrderList(billingOrderListDtoList);
                    list.add(billingAndOrderListDto);
                }
                exportXlsx(list, type);
            }
        } else {

            if (billNos != null && billNos.size() > 0) {
                for (String billNo : billNos) {
                    List<BillingAndOrderListDto> Dtos = new ArrayList<BillingAndOrderListDto>();
                    BillingListDto billingListDto = findBillingInfo(billNo, type);
                    BillingAndOrderListDto billingAndOrderListDto = new BillingAndOrderListDto();
                    EntityUtils.copyPropertiesIgnoreNull(billingListDto, billingAndOrderListDto);
                    ParamData pd = new ParamData();
                    pd.put("billNo", billNo);
                    pd.put("type", type);
                    Billing billing = findByBillNo(billNo, type);
                    if (billing != null) {
                        pd.put("supplier", billing.getCustomerId());
                    }
                    // 账单下所有订单
                    List<BillingOrderListStringDto> billingOrderListDtoList = billingOrderRelService.ordersByBillNoAndSupplier(pd, "findOrdersBySupplier");
                    billingAndOrderListDto.setOrderList(billingOrderListDtoList);
                    Dtos.add(billingAndOrderListDto);
                    exportXlsxSupplier(Dtos, type);
                }
            }
        }

    }

    private void exportXlsx(List<BillingAndOrderListDto> list, String type) {
        for (BillingAndOrderListDto dto : list) {
            StorePath storePath;
            String filename = "";
            String filePath = "";
            // 第一步，创建一个webbook，对应一个Excel文件
            SXSSFWorkbook wb = new SXSSFWorkbook(1000);
            String bill = dto.getBillNo();
            filename = dto.getCustomerName() + "(" + dto.getCustomerCode() + ")" + bill + "账单.xlsx";
            List<BillingOrderListStringDto> orderList = dto.getOrderList();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            List<List<BillingOrderListStringDto>> lists = new ArrayList<List<BillingOrderListStringDto>>();
            if (orderList.size() > 65500) {
                int count = orderList.size() / 65500;
                int mod = orderList.size() % 65500;
                int num = 0;
                if (mod > 0) {
                    num = (int) Math.floor(count + 1);
                } else {
                    num = (int) Math.floor(count);
                }
                for (int i = 0; i < num; i++) {
                    List<BillingOrderListStringDto> listOrder = new ArrayList<BillingOrderListStringDto>();
                    if (i < num) {
                        for (int j = 0; j < MAX_SIZE; j++) {
                            BillingOrderListStringDto billingOrderListDto = orderList.get(i * 65500 + j);
                            listOrder.add(billingOrderListDto);
                        }
                    } else {
                        for (int j = 0; j < mod; j++) {
                            BillingOrderListStringDto billingOrderListDto = orderList.get(i * 65500 + j);
                            listOrder.add(billingOrderListDto);
                        }
                    }
                    lists.add(listOrder);
                }
                setCell(wb, dto, bill, lists);
            } else {
                lists.add(orderList);
                setCell(wb, dto, bill, lists);
            }
            try {
                // 第六步，将文件上传文件服务器
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                wb.write(os);
                ByteArrayInputStream swapStream = new ByteArrayInputStream(os.toByteArray());
                storePath = storageClient.uploadFile(swapStream, os.toByteArray().length,
                        FilenameUtils.getExtension("export.xlsx"), null);
                // 第七步，更新数据库
                ParamData pd = new ParamData();
                String url = storePath.getFullPath() + "?filename=" + filename;
                pd.put("url", url);
                pd.put("billNo", dto.getBillNo());
                pd.put("type", type);
                dao.update(getSqlName("updateURL"), pd);
                os.close();
                wb.close();
            } catch (Exception e) {
                System.out.println(e.toString());
                e.printStackTrace();
            }
        }

    }

    private void exportXlsxSupplier(List<BillingAndOrderListDto> list, String type) {
        for (BillingAndOrderListDto dto : list) {
            StorePath storePath;
            String filename = "";
            String filePath = "";
            // 第一步，创建一个webbook，对应一个Excel文件
            SXSSFWorkbook wb = new SXSSFWorkbook(1000);
            String bill = dto.getBillNo();
            filename = dto.getCustomerName() + "(" + dto.getCustomerCode() + ")" + bill + "账单.xlsx";
            List<BillingOrderListStringDto> orderList = dto.getOrderList();
            // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
            List<List<BillingOrderListStringDto>> lists = new ArrayList<List<BillingOrderListStringDto>>();
            if (orderList.size() > 65500) {
                int count = orderList.size() / 65500;
                int mod = orderList.size() % 65500;
                int num = 0;
                if (mod > 0) {
                    num = (int) Math.floor(count + 1);
                } else {
                    num = (int) Math.floor(count);
                }
                for (int i = 0; i < num; i++) {
                    List<BillingOrderListStringDto> listOrder = new ArrayList<BillingOrderListStringDto>();
                    if (i < num) {
                        for (int j = 0; j < MAX_SIZE; j++) {
                            BillingOrderListStringDto billingOrderListDto = orderList.get(i * 65500 + j);
                            listOrder.add(billingOrderListDto);
                        }
                    } else {
                        for (int j = 0; j < mod; j++) {
                            BillingOrderListStringDto billingOrderListDto = orderList.get(i * 65500 + j);
                            listOrder.add(billingOrderListDto);
                        }
                    }
                    lists.add(listOrder);
                }
                setCellSupplier(wb, dto, bill, lists);
            } else {
                lists.add(orderList);
                setCellSupplier(wb, dto, bill, lists);
            }
            try {
                // 第六步，将文件上传文件服务器
                ByteArrayOutputStream os = new ByteArrayOutputStream();
                wb.write(os);
                ByteArrayInputStream swapStream = new ByteArrayInputStream(os.toByteArray());
                storePath = storageClient.uploadFile(swapStream, os.toByteArray().length,
                        FilenameUtils.getExtension("export.xlsx"), null);
                // 第七步，更新数据库
                ParamData pd = new ParamData();
                String url = storePath.getFullPath() + "?filename=" + filename;
                pd.put("url", url);
                pd.put("billNo", dto.getBillNo());
                pd.put("type", type);
                dao.update(getSqlName("updateURL"), pd);
                os.close();
                wb.close();
            } catch (Exception e) {
                System.out.println(e.toString());
                e.printStackTrace();
            }
        }

    }

    /**
     * 只有已出帐账单可以导出
     *
     * @param billNos
     */
    private void checkExportBills(List<String> billNos) {
        ParamData paramData = new ParamData();
        paramData.put("billNos", billNos);
        List<Billing> bills = dao.findForList(getSqlName("findActivedByBillNos"), paramData, Billing.class);
        if (null != bills && bills.size() > 0) {
            for (Billing bill : bills) {
                if (!bill.getStatus().getCode().equals(BillingStatus.CHARGEOFF.getCode())
                        && !bill.getStatus().getCode().equals(BillingStatus.PAID.getCode())
                        && !bill.getStatus().getCode().equals(BillingStatus.OVERDUE.getCode())) {
                    throw Exceptions.bizException(ErrorCodes.ERROR_FINANCIAL_CHARGEOFF_CAN_EXPORT);
                }
            }
        }
    }

    /**
     * 填充cell值
     *
     * @param wb
     * @param dto
     * @param bill
     * @param lists
     */
    private void setCell(SXSSFWorkbook wb, BillingAndOrderListDto dto, String bill, List<List<BillingOrderListStringDto>> lists) {
        int sheetPage = 0;
        for (List<BillingOrderListStringDto> orderList : lists) {
            SXSSFSheet sheet = wb.createSheet(bill + "-" + String.valueOf(sheetPage));
            sheet.trackAllColumnsForAutoSizing();
            sheetPage = sheetPage + 1;
            // 第三步，创建单元格，并设置值表头 设置表头居中
            CellStyle style = wb.createCellStyle();
            style.setWrapText(true);
            // 第四步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            SXSSFRow row0;
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            setCellHead(wb, sheet, style, "账单号", dto.getBillNo(), 0, null, 0);
            setCellHead(wb, sheet, style, "客户名", dto.getCustomerName() + "(" + dto.getCustomerCode() + ")", 1, null, 0);
            setCellHead(wb, sheet, style, "账单周期", (DateUtils.formatDetailDate(dto.getStartEffectTime())) + " ~ "
                    + (DateUtils.formatDetailDate(dto.getEndEffectTime())), 2, null, 0);
            setCellHead(wb, sheet, style, "生成时间", (DateUtils.formatDetailDate(dto.getReleaseTime())), 3, null, 0);
            setCellHead(wb, sheet, style, "状态", dto.getStatus().getName(), 4, null, 0);
            setCellHead(wb, sheet, style, "币种", dto.getCurrencyType(), 5, null, 0);
            BigDecimal total = dto.getTotal();
            if (null != total) {
                total = total.setScale(3, BigDecimal.ROUND_HALF_UP);
            }
            BigDecimal actualTotal = dto.getActualTotal();
            if (null != actualTotal) {
                actualTotal = actualTotal.setScale(2, BigDecimal.ROUND_HALF_UP);
            } else {
                actualTotal = dto.getTotal().setScale(2, BigDecimal.ROUND_HALF_UP);
            }
            setCellHead(wb, sheet, style, "应收总计", total.toString(), 6, null, 0);
            row0 = setCellHead(wb, sheet, style, "价格调整", dto.getAdjustment(), 7, null, 0);
            setCellHead(wb, sheet, style, "调整说明", dto.getAdjustmentRemark(), 7, row0, 2);
            setCellHead(wb, sheet, style, "实收总计", actualTotal.toString(), 8, null, 0);
            SXSSFRow row9 = sheet.createRow(9);
            SXSSFRow row10 = sheet.createRow(10);

            SXSSFRow row11 = sheet.createRow(11);
            SXSSFCell cell11 = row11.createCell(0);
            cell11.setCellValue("运单号");
            cell11 = row11.createCell(1);
            cell11.setCellValue("账号名");
            cell11 = row11.createCell(2);
            cell11.setCellValue("产品名称");
            cell11 = row11.createCell(3);
            cell11.setCellValue("计费时间");
            cell11 = row11.createCell(4);
            cell11.setCellValue("价格明细(" + dto.getCurrencyType() + ")");

            // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
            for (int i = 0; i < orderList.size(); i++) {
                row11 = sheet.createRow(i + 12);
                BillingOrderListStringDto billingOrderListDto = orderList.get(i);
                // 第四步，创建单元格，并设置值
                row11.createCell(0).setCellValue(billingOrderListDto.getWaybillNo());
                row11.createCell(1).setCellValue(
                        billingOrderListDto.getCustomerName() + "(" + billingOrderListDto.getCustomerUserName() + ")");
                row11.createCell(2).setCellValue(billingOrderListDto.getProductName());
                SXSSFCell cell = row11.createCell(3);
                cell.setCellValue(DateUtils.formatDetailDate(billingOrderListDto.getBillTime()));
                List<Map<String, Object>> listmap = billingOrderListDto.getOrderFeeList();
                StringBuffer sb = new StringBuffer();
                if (listmap != null) {

                    for (Map map : listmap) {
                        sb.append(map.get("type"));
                        sb.append(": ");
                        String fee = null == map.get("value") ? "0" : map.get("value") + "";

                        sb.append(new BigDecimal(fee).setScale(3, BigDecimal.ROUND_HALF_UP));
                        sb.append("\r\n");
                    }
                }
                sb.append("总计: " + billingOrderListDto.getAmountDue());
                // 设置行高
                if (null != listmap && !listmap.isEmpty()) {
                    row11.setHeight((short) ((listmap.size() + 1) * 512));
                }
                CellStyle cellstyle = wb.createCellStyle();
                cellstyle.setWrapText(true);
                SXSSFCell cell4 = row11.createCell(4);
                cell4.setCellStyle(cellstyle);
                cell4.setCellValue(new XSSFRichTextString(sb.toString()));
            }
            // 自动列宽
            sheet.autoSizeColumn(0); // 调整第一列宽度
            sheet.autoSizeColumn(1); // 调整第二列宽度
            sheet.autoSizeColumn(2); // 调整第三列宽度
            sheet.autoSizeColumn(3); // 调整第四列宽度
            sheet.autoSizeColumn(4); // 调整第四列宽度
        }
    }

    /**
     * 填充cell值
     *
     * @param wb
     * @param dto
     * @param bill
     * @param lists
     */
    private void setCellSupplier(SXSSFWorkbook wb, BillingAndOrderListDto dto, String bill, List<List<BillingOrderListStringDto>> lists) {
        int sheetPage = 0;
        for (List<BillingOrderListStringDto> orderList : lists) {
            SXSSFSheet sheet = wb.createSheet(bill + "-" + String.valueOf(sheetPage));
            sheet.trackAllColumnsForAutoSizing();
            sheetPage = sheetPage + 1;
            // 第三步，创建单元格，并设置值表头 设置表头居中
            CellStyle style = wb.createCellStyle();
            style.setWrapText(true);
            // 第四步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
            SXSSFRow row0;
            style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
            setCellHead(wb, sheet, style, "账单号", dto.getBillNo(), 0, null, 0);
            setCellHead(wb, sheet, style, "客户名", dto.getCustomerName() + "(" + dto.getCustomerCode() + ")", 1, null, 0);
            setCellHead(wb, sheet, style, "账单周期", (DateUtils.formatDetailDate(dto.getStartEffectTime())) + " ~ "
                    + (DateUtils.formatDetailDate(dto.getEndEffectTime())), 2, null, 0);
            setCellHead(wb, sheet, style, "生成时间", (DateUtils.formatDetailDate(dto.getReleaseTime())), 3, null, 0);
            setCellHead(wb, sheet, style, "状态", dto.getStatus().getName(), 4, null, 0);
            setCellHead(wb, sheet, style, "币种", dto.getCurrencyType(), 5, null, 0);
            BigDecimal total = dto.getTotal();
            if (null != total) {
                total = total.setScale(3, BigDecimal.ROUND_HALF_UP);
            }
            BigDecimal actualTotal = dto.getActualTotal();
            if (null != actualTotal) {
                actualTotal = actualTotal.setScale(2, BigDecimal.ROUND_HALF_UP);
            } else {
                actualTotal = dto.getTotal().setScale(2, BigDecimal.ROUND_HALF_UP);
            }
            setCellHead(wb, sheet, style, "应收总计", total.toString(), 6, null, 0);
            row0 = setCellHead(wb, sheet, style, "价格调整", dto.getAdjustment(), 7, null, 0);
            setCellHead(wb, sheet, style, "调整说明", dto.getAdjustmentRemark(), 7, row0, 2);
            setCellHead(wb, sheet, style, "实收总计", actualTotal.toString(), 8, null, 0);
            SXSSFRow row9 = sheet.createRow(9);
            SXSSFRow row10 = sheet.createRow(10);

            SXSSFRow row11 = sheet.createRow(11);
            SXSSFCell cell11 = row11.createCell(0);
            cell11.setCellValue("运单号");
            cell11 = row11.createCell(1);
            cell11.setCellValue("服务名称");
            cell11 = row11.createCell(2);
            cell11.setCellValue("计费时间");
            cell11 = row11.createCell(3);
            cell11.setCellValue("价格明细(" + dto.getCurrencyType() + ")");

            // 第五步，写入实体数据 实际应用中这些数据从数据库得到，
            for (int i = 0; i < orderList.size(); i++) {
                row11 = sheet.createRow(i + 12);
                BillingOrderListStringDto billingOrderListDto = orderList.get(i);
                // 第四步，创建单元格，并设置值
                row11.createCell(0).setCellValue(billingOrderListDto.getWaybillNo());
                row11.createCell(1).setCellValue(
                        billingOrderListDto.getServiceName());
                SXSSFCell cell = row11.createCell(2);
                cell.setCellValue(DateUtils.formatDetailDate(billingOrderListDto.getBillTime()));
                List<Map<String, Object>> listmap = billingOrderListDto.getOrderFeeList();
                StringBuffer sb = new StringBuffer();
                if (listmap != null) {

                    for (Map map : listmap) {
                        sb.append(map.get("type"));
                        sb.append(": ");
                        String fee = null == map.get("value") ? "0" : map.get("value") + "";

                        sb.append(new BigDecimal(fee).setScale(3, BigDecimal.ROUND_HALF_UP));
                        sb.append("\r\n");
                    }
                }
                sb.append("总计: " + billingOrderListDto.getAmountDue());
                // 设置行高
                if (null != listmap && !listmap.isEmpty()) {
                    row11.setHeight((short) ((listmap.size() + 1) * 512));
                }
                CellStyle cellstyle = wb.createCellStyle();
                cellstyle.setWrapText(true);
                SXSSFCell cell3 = row11.createCell(3);
                cell3.setCellStyle(cellstyle);
                cell3.setCellValue(new XSSFRichTextString(sb.toString()));
            }
            // 自动列宽
            sheet.autoSizeColumn(0); // 调整第一列宽度
            sheet.autoSizeColumn(1); // 调整第二列宽度
            sheet.autoSizeColumn(2); // 调整第三列宽度
            sheet.autoSizeColumn(3); // 调整第四列宽度
        }
    }

    /**
     * 填充表头
     *
     * @param wb
     * @param sheet
     * @param style
     * @param key
     * @param value
     * @param index
     */
    private static SXSSFRow setCellHead(SXSSFWorkbook wb, SXSSFSheet sheet, CellStyle style, String key, String value,
                                        int index, SXSSFRow row, int cellIndex) {
        if (null == row) {
            row = sheet.createRow(index);
        }
        style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
        SXSSFCell cell = row.createCell(cellIndex);
        cell.setCellValue(key);
        cell.setCellStyle(style);
        cell = row.createCell(cellIndex + 1);
        cell.setCellValue(value);
        return row;
    }

    /**
     * 导出账单-账单基本信息
     *
     * @param billNo
     * @return
     */
    private BillingListDto findBillingInfo(String billNo, String type) {
        ParamData paramData = new ParamData();
        paramData.put("billNo", billNo);
        paramData.put("type", type);
        BillingListDto billingListDto = new BillingListDto();
        if (TrdConstants.BillType.RECEIVALE.equals(type)) {
            billingListDto = (BillingListDto) dao.findForObject(getSqlName("findBillingBySupplierInfo"), paramData);
        } else {
            billingListDto = (BillingListDto) dao.findForObject(getSqlName("findBillingInfo"), paramData);
        }
        if (null != billingListDto) {
            billingListDto.setAdjustment(adjustment(billingListDto.getAdjustmentTypeCode(), billingListDto.getAdjustmentValue(), billingListDto.getAdjustmentUnit()));
        }
        return billingListDto;
    }

    /**
     * 提交审核
     *
     * @param billNos 账单号集合
     */
    @Override
    public void submit(List<String> billNos, String type) {
        if (CollectionUtils.isEmpty(billNos)) {
            return;
        }
        // 修改账单状态,重置是否打回过草稿状态
        updateBillingStatus(billNos, BillingStatus.DRAFT, BillingStatus.AUDITING, "", "账单审核", false, type);
    }

    /**
     * 审核账单
     *
     * @param map
     */
    @Override
    public BillingStatus audit(Map<String, Object> map, String billType) {
        List<String> billNos = (List<String>) map.get("billNos");
        Object type = map.get("type");
        Object msg = map.get("msg");
        if (CollectionUtils.isEmpty(billNos) || null == type || null == msg) {
            throw Exceptions.bizException(ErrorCodes.ERROR_ARGUMENT_EMPTY, "");
        }
        BillingStatus statusTo = null;
        try {
            statusTo = BillingStatus.valueOf(String.valueOf(type));
        } catch (Exception e) {
            e.printStackTrace();
            throw Exceptions.bizException(ErrorCodes.ERROR_ARGUMENT_EMPTY, "");
        }
        BillingStatus statusFrom = null;
        Boolean isReject = false; // 打回草稿判断
        switch (statusTo) {
            case DRAFT:
                statusFrom = BillingStatus.AUDITING;
                isReject = true;
                break;
            case CHARGEOFF:
                statusFrom = BillingStatus.AUDITING;
                break;
            case PAID:
                statusFrom = BillingStatus.CHARGEOFF;
                break;
            default:
                break;
        }
        // 修改账单状态
        updateBillingStatus(billNos, statusFrom, statusTo, String.valueOf(msg), "账单审核及已付款", isReject, billType);
        return statusTo;
    }

    /**
     * 价格调整
     *
     * @param billingDto
     */
    @Override
    public void adjustment(Map<String, String> billingDto, String type) {
        String billNo = billingDto.get("billNo");
        String adjustmentTypeCode = billingDto.get("adjustmentTypeCode");
        String adjustmentValue = billingDto.get("adjustmentValue");
        String adjustmentUnit = billingDto.get("adjustmentUnit");
        String adjustmentRemark = billingDto.get("adjustmentRemark");
        if (StringUtils.isEmpty(adjustmentTypeCode) || StringUtils.isEmpty(adjustmentValue) || StringUtils.isEmpty(adjustmentUnit) || StringUtils.isEmpty(adjustmentRemark)) {
            throw Exceptions.bizException(ErrorCodes.ERROR_ARGUMENT_ILLEGAL, "");
        }
        // 校验调整价格数值
        checkAdjustmentValue(adjustmentValue, adjustmentUnit);
        // 只有待审核状态下可以调整
        Billing billing = findByBillNo(billNo, type);
        if (null != billing && billing.getStatus().getCode().equals(BillingStatus.AUDITING.getCode())) {
            // 价格调整数据
            saveAdjustment(billNo, adjustmentTypeCode, adjustmentValue, adjustmentUnit, adjustmentRemark, type);
            // 审核日志
            String result = populateAdjustment(adjustmentTypeCode, adjustmentValue, adjustmentUnit);
            if (!StringUtils.isEmpty(result)) {
                result = "[" + result + "] ";
            }
            approvalService.approval(ApprovalService.ENTITY_TYPE_FINANCIAL, billing.getBillNo(), BillingStatus.AUDITING, BillingStatus.ADJUSTPRICE, result + adjustmentRemark, PrincipalUtils.getAccountId());
        } else {
            throw Exceptions.bizException(ErrorCodes.ERROR_FINANCIAL_AUDITING_CAN_ADJUST_PRICE);
        }
    }

    /**
     * 调整价格
     *
     * @param billNo
     * @param adjustmentTypeCode
     * @param adjustmentValue
     * @param adjustmentUnit
     * @param adjustmentRemark
     */
    private void saveAdjustment(String billNo, String adjustmentTypeCode, String adjustmentValue, String adjustmentUnit, String adjustmentRemark, String type) {
        // 获取数据库当前时间
        Date now = dao.getDbDate();
        // 计算价格价格调整后实收费
        BigDecimal actualTotal = countFeeAfterAdjustment(billNo, adjustmentTypeCode, adjustmentValue, adjustmentUnit, type);
        //实收价格保留两位小数
        if (null != actualTotal) {
            actualTotal = actualTotal.setScale(2, BigDecimal.ROUND_HALF_UP);
        }
        // 价格调整数据
        BillingDto billingDtoNew = new BillingDto();
        billingDtoNew.setBillNo(billNo);
        billingDtoNew.setAdjustmentTypeCode(BillingAdjustmentTypeCode.valueOf(adjustmentTypeCode));
        billingDtoNew.setAdjustmentValue(new BigDecimal(adjustmentValue));
        billingDtoNew.setAdjustmentUnit(adjustmentUnit);
        billingDtoNew.setAdjustmentRemark(adjustmentRemark);
        billingDtoNew.setAdjustmentByUser(PrincipalUtils.getAccountId());
        billingDtoNew.setAdjustmentTime(now);
        billingDtoNew.setActualTotal(actualTotal);
        billingDtoNew.setType(type);
        dao.update(getSqlName("updateSelective"), billingDtoNew);
    }

    /**
     * 当调整的为“百分比”时，中间数值为5位（小数点精确到后三位） 当调整的为“金额”时，中间数值为10位（小数点精确到后三位）
     *
     * @param adjustmentValue
     * @param adjustmentUnit
     */
    private void checkAdjustmentValue(String adjustmentValue, String adjustmentUnit) {
        if (QuotationConstants.DiscountType.PERCENTAGE.equals(adjustmentUnit)) { // 百分比
            if (!isNumber(adjustmentValue.toString(), PERCENT_LENGTH)) {
                throw Exceptions.bizException(ErrorCodes.ERROR_FINANCIAL_ADJUSTMENT_VALUE_ERROR);
            }
        } else if (QuotationConstants.DiscountType.AMOUNT.equals(adjustmentUnit)) { // 金额
            if (!isNumber(adjustmentValue.toString(), AMOUNT_LENGTH)) {
                throw Exceptions.bizException(ErrorCodes.ERROR_FINANCIAL_ADJUSTMENT_VALUE_ERROR);
            }
        }
    }

    /**
     * 判断是否有效数字
     *
     * @param str
     * @return
     */
    private boolean isNumber(String str, int length) {
        String temp = "^(([1-9]{1}\\d*)|([0]{1}))(\\.(\\d){0,3})?$";
        Pattern pattern = Pattern.compile(temp);
        Matcher match = pattern.matcher(str);
        return match.matches() && str.length() <= length;
    }

    /**
     * 记录调整行为记录
     *
     * @param adjustmentTypeCode
     * @param adjustmentValue
     * @param adjustmentUnit
     * @return
     */
    private String populateAdjustment(String adjustmentTypeCode, String adjustmentValue, String adjustmentUnit) {
        String result = "";
        // 上调
        if (adjustmentTypeCode.equals(BillingAdjustmentTypeCode.UPPER.name())) {
            // 1： 百分比； 2： 金额
            if (QuotationConstants.DiscountType.PERCENTAGE.equals(adjustmentUnit)) {
                result = "+" + adjustmentValue + "%";
            } else if (QuotationConstants.DiscountType.AMOUNT.equals(adjustmentUnit)) {
                result = "+" + adjustmentValue;
            }
        } else if (adjustmentTypeCode.equals(BillingAdjustmentTypeCode.DOWN.name())) { // 下调
            if (QuotationConstants.DiscountType.PERCENTAGE.equals(adjustmentUnit)) {
                result = "-" + adjustmentValue + "%";
            } else if (QuotationConstants.DiscountType.AMOUNT.equals(adjustmentUnit)) {
                result = "-" + adjustmentValue;
            }
        }
        return result;
    }

    /**
     * 计算价格价格调整后实收费
     *
     * @param billNo
     * @param adjustmentTypeCode
     * @param adjustmentValue
     * @param adjustmentUnit
     * @return
     */
    private BigDecimal countFeeAfterAdjustment(String billNo, String adjustmentTypeCode, String adjustmentValue, String adjustmentUnit, String type) {
        // 获取账单详细信息
        Billing billing = findByBillNo(billNo, type);
        if (null != billing) {
            // 判断价格调整类型
            BigDecimal actualTotal = null;
            BigDecimal total = billing.getTotal();
            // 上调
            if (adjustmentTypeCode.equals(BillingAdjustmentTypeCode.UPPER.name())) {
                if (QuotationConstants.DiscountType.PERCENTAGE.equals(adjustmentUnit)) {
                    actualTotal = total.multiply(BigDecimal.valueOf(1).add(new BigDecimal(adjustmentValue).divide(BigDecimal.valueOf(100), 3, BigDecimal.ROUND_UP)));
                } else if (QuotationConstants.DiscountType.AMOUNT.equals(adjustmentUnit)) {
                    actualTotal = total.add(new BigDecimal(adjustmentValue));
                }
            } else if (adjustmentTypeCode.equals(BillingAdjustmentTypeCode.DOWN.name())) { // 下调
                if (QuotationConstants.DiscountType.PERCENTAGE.equals(adjustmentUnit)) {
                    actualTotal = total.multiply(BigDecimal.valueOf(1).subtract(new BigDecimal(adjustmentValue).divide(BigDecimal.valueOf(100), 3, BigDecimal.ROUND_UP)));
                } else if (QuotationConstants.DiscountType.AMOUNT.equals(adjustmentUnit)) {
                    actualTotal = total.subtract(new BigDecimal(adjustmentValue));
                }
            }
            return actualTotal;
        }
        return null;
    }

    /**
     * 修改账单状态
     *
     * @param billNos            账单号
     * @param approvalStatusFrom 账单当前状态
     * @param approvalStatusTo   要修改成的状态
     * @param msg                流转信息
     * @param logMsg             日志信息
     */
    private void updateBillingStatus(List<String> billNos, BillingStatus approvalStatusFrom, BillingStatus approvalStatusTo, String msg, String logMsg, Boolean isReject, String type) {
        Map<String, Object> paramMap = new HashMap<>();
        paramMap.put("billNos", billNos);
        paramMap.put("type", type);
        List<Billing> bills = dao.findForList(getSqlName("findActivedByBillNos"), paramMap, Billing.class);
        List<String> billNoSaved = new ArrayList<>();
        for (Billing billing : bills) {
            if (approvalStatusFrom.getCode().equals(billing.getStatus().getCode())) {
                billNoSaved.add(billing.getBillNo());
                // 审核日志
                approvalService.approval(ApprovalService.ENTITY_TYPE_FINANCIAL, billing.getBillNo(), approvalStatusFrom, approvalStatusTo, msg, PrincipalUtils.getAccountId());
            } else {
                // 禁止状态跳跃
                throw Exceptions.bizException(ErrorCodes.ERROR_FINANCIAL_STATUS_CAN_NOT_JUMP, approvalStatusFrom.getName(), approvalStatusTo.getName());
            }
        }
        if (CollectionUtils.isEmpty(billNoSaved)) {
            return;
        }
        paramMap = new HashMap<>();
        paramMap.put("type", type);
        paramMap.put("status", approvalStatusTo);
        paramMap.put("billNos", billNoSaved);
        paramMap.put("lastUpdateUserId", PrincipalUtils.getAccountId());
        paramMap.put("isRejected", isReject);
        if (approvalStatusTo.getCode().equals(BillingStatus.CHARGEOFF.getCode())) {
            paramMap.put("billTime", dao.getDbDate());
        }
        dao.update(getSqlName("updateStatus"), paramMap);
        if (approvalStatusTo.getCode().equals(BillingStatus.CHARGEOFF.getCode())) {
            // 生成账单excel更新账单文件路径
            exportBills(billNos, type);
        }
        // 保存操作日志
        Map<String, Object> dataBackup = new HashMap<>();
        dataBackup.put("UPDATE_BILLING", paramMap);
        PrincipalLogUtils.addOperationLog(ModuleNames.BIZ, ModuleNames.BIZ_FINANCIAL, UserActions.UPDATE, logMsg, dataBackup);
    }

    /**
     * 重新生成账单
     *
     * @param customerId
     * @param startEffectTime
     * @param endEffectTime
     */
    @Override
    public void regenerate(String taskCode, Integer customerId, Date startEffectTime, Date endEffectTime, String type) {
        BillingTaskDto billingTaskDto = new BillingTaskDto();
        billingTaskDto.setTaskCode(taskCode);
        billingTaskDto.setCustomerId(customerId);
        billingTaskDto.setStartEffectTime(startEffectTime);
        billingTaskDto.setEndEffectTime(endEffectTime);
        billingTaskDto.setType(type);
        generateBill(billingTaskDto);
    }

    /**
     * 根据任务编码获取任务
     *
     * @param taskCodes
     * @return
     */
    public List<Billing> findByTaskCodes(List<String> taskCodes, String type) {
        ParamData paramData = new ParamData();
        paramData.put("taskCodes", taskCodes);
        paramData.put("type", type);
        return dao.findForList(getSqlName("findByTaskCodes"), paramData, Billing.class);
    }

    /**
     * 生成账单
     *
     * @param billingTaskDto
     */
    @Override
    public void generateBill(BillingTaskDto billingTaskDto) {
        //生成主帐号账单
        newAndRetryGenerate(billingTaskDto);
    }


    /**
     * 根据客户，账单周期获取账单信息
     *
     * @param customerId
     * @param startEffectTime
     * @param endEffectTime
     * @return
     */
    private List<Billing> findByCustomerIdAndStartEffectTimeAndEndEffectTime(Integer customerId, Date startEffectTime, Date endEffectTime, String type) {
        ParamData paramData = new ParamData();
        paramData.put("customerId", customerId);
        paramData.put("startEffectTime", startEffectTime);
        paramData.put("endEffectTime", endEffectTime);
        paramData.put("type", type);
        List<Billing> billingList = dao.findForList(getSqlName("findByCustomerIdAndStartEffectTimeAndEndEffectTime"), paramData, Billing.class);
        return billingList;
    }

    /**
     * 生成或者重新生成账单
     *
     * @param billingTaskDto
     */
    private void newAndRetryGenerate(BillingTaskDto billingTaskDto) {
        // 验证账单周期时间
        validateStartEndTime(billingTaskDto);
        // 获取(用户)时间段内未生成账单的所有订单
        List<BillingGenerateDto> ordServiceBillList = getOrderByCustomerIdAndCreateTime(billingTaskDto, false);
        // 获取(用户)时间段内已经生成账单的所有订单
        List<BillingGenerateDto> ordServiceBillListGenerated = getOrderByCustomerIdAndCreateTime(billingTaskDto, true);
        // 更新已经跳过订单数
        //if (null != ordServiceBillListGenerated && ordServiceBillListGenerated.size() > 0) {
        //    BillingTask oldBillTask = billingTaskService.findByTaskCode(billingTaskDto.getTaskCode(), billingTaskDto.getType());
        //    int qty = oldBillTask.getSkippedOrderQuantity() == null ? 0 : oldBillTask.getSkippedOrderQuantity();
        //    BillingTask ordersGenerated = new BillingTask();
        //    ordersGenerated.setTaskCode(billingTaskDto.getTaskCode());
        //   ordersGenerated.setSkippedOrderQuantity(qty + ordServiceBillListGenerated.size());
        //    ordersGenerated.setType(billingTaskDto.getType());
        //    billingTaskService.updateBillingTask(ordersGenerated);
        //}
        // 添加新的账单
        if (null != ordServiceBillList && ordServiceBillList.size() > 0) {
            if (null != billingTaskDto.getCustomerId()) {
                Billing billing = populateBilling(billingTaskDto.getCustomerId(), billingTaskDto.getStartEffectTime(),
                        billingTaskDto.getEndEffectTime(), billingTaskDto.getTaskCode(), ordServiceBillList,
                        ordServiceBillListGenerated, billingTaskDto.getType());
                saveBilling(billing, ordServiceBillList);
            } else {
                Map<Integer, List<BillingGenerateDto>> map = populateGenerateData(ordServiceBillList);
                if (!map.isEmpty()) {
                    for (Map.Entry<Integer, List<BillingGenerateDto>> entry : map.entrySet()) {
                        Billing billing = populateBilling(entry.getKey(), billingTaskDto.getStartEffectTime(),
                                billingTaskDto.getEndEffectTime(), billingTaskDto.getTaskCode(), entry.getValue(),
                                ordServiceBillListGenerated, billingTaskDto.getType());
                        saveBilling(billing, entry.getValue());
                    }
                }
            }
        }
    }

    /**
     * 没有客户的订单数据准备
     *
     * @param ordServiceBillList
     * @return
     */
    private Map<Integer, List<BillingGenerateDto>> populateGenerateData(List<BillingGenerateDto> ordServiceBillList) {
        Map<Integer, List<BillingGenerateDto>> map = new HashMap<>();
        for (BillingGenerateDto billingGenerateDto : ordServiceBillList) {
            List<BillingGenerateDto> customerOrderList = map.get(billingGenerateDto.getCustomerId());
            if (null == customerOrderList) {
                customerOrderList = new ArrayList<>();
            }
            customerOrderList.add(billingGenerateDto);
            map.put(billingGenerateDto.getCustomerId(), customerOrderList);
        }
        return map;
    }

    /**
     * 保存账单
     *
     * @param billing
     * @param orderList
     */
    private void saveBilling(Billing billing, List<BillingGenerateDto> orderList) {
        try {
            dao.save(getSqlName("insertSelective"), billing);
            saveBillingOrderRel(billing.getTaskCode(), billing.getBillNo(), orderList, billing.getType(), billing.getCustomerId());
            // 更新账单任务已经生成账单客户数量
            billingTaskService.updateTaskCustomerComplete(billing.getTaskCode(), orderList.size(), billing.getType());
            // 审核日志
            approvalService.approval(ApprovalService.ENTITY_TYPE_FINANCIAL, billing.getBillNo(), BillingStatus.GENERATION, BillingStatus.DRAFT, "生成账单", PrincipalUtils.getAccountId());
        } catch (Exception e) {
            List<String> taskCodes = new ArrayList<>();
            taskCodes.add(billing.getTaskCode());
            billingTaskService.updateTaskStatus(BillingTaskStatus.SUSPEND, taskCodes, billing.getType());
            // 更新订单为未生成订单状态
            updateIsBilled(orderList, billing.getType(), billing.getCustomerId());
            e.printStackTrace();
        }
    }

    /**
     * 更新订单是否已经生成订单状态
     *
     * @param orderList
     */
    private void updateIsBilled(List<BillingGenerateDto> orderList, String type, Integer supplier) {
        List<String> orderNos = new ArrayList<>();
        for (BillingGenerateDto billingGenerateDto : orderList) {
            orderNos.add(billingGenerateDto.getOrderNo());
        }
        // 更新订单为已经生成订单状态
        ordServiceBillService.updateIsBilledStatus(orderNos, false, type, supplier);
    }

    /**
     * 保存账单订单关联
     *
     * @param taskCode
     * @param billNo
     * @param customerOrderList
     */
    private void saveBillingOrderRel(String taskCode, String billNo, List<BillingGenerateDto> customerOrderList, String type, Integer supplier) {
        // 根据账单号删除账单下的所有订单
        billingOrderRelService.deleteByBillNo(billNo, type, supplier);
        // 添加新信息
        for (BillingGenerateDto billingGenerateDto : customerOrderList) {
            BillingOrderRel billingOrderRel = new BillingOrderRel();
            billingOrderRel.setTaskCode(taskCode);
            billingOrderRel.setBillNo(billNo);
            billingOrderRel.setOrderNo(billingGenerateDto.getOrderNo());
            billingOrderRel.setIsDeleted(false);
            billingOrderRel.setType(type);
            billingOrderRelService.save(billingOrderRel);
            // 更新订单为已经生成订单状态
            List<String> orderNos = new ArrayList<>();
            orderNos.add(billingGenerateDto.getOrderNo());
            ordServiceBillService.updateIsBilledStatus(orderNos, true, type, billingGenerateDto.getCustomerId());
        }
    }

    /**
     * 账单列表
     *
     * @param params
     * @return
     */
    @Override
    public QResultDto findByFilter(QueryParams params) {
        ParamData pd = convertQueryParams(params);
        List<BillingListStringDto> billingListStringDtos = new ArrayList<>();
        String type = pd.getString("type");
        List<BillingListDto> billingList = new ArrayList<BillingListDto>();
        //根据条件获取账单列表
        if (QuotationConstants.DiscountType.PERCENTAGE.equals(type)) {
            billingList = dao.findForList(getSqlName("findBySupplierFilterListPage"), pd, BillingListDto.class);
        } else {
            billingList = dao.findForList(getSqlName("findByFilterListPage"), pd, BillingListDto.class);
        }
        if (null != billingList && billingList.size() > 0) {
            billingList.forEach(billing -> {
                billing.setStatusName(billing.getStatus().getName());
                billing.setAdjustment(adjustment(billing.getAdjustmentTypeCode(), billing.getAdjustmentValue(), billing.getAdjustmentUnit()));
                //应收保留三位小数
                BigDecimal totalDecimal3 = billing.getTotal().setScale(3, BigDecimal.ROUND_HALF_UP);
                String total = billing.getTotal().setScale(3, BigDecimal.ROUND_HALF_UP).toString();
                //实收保留两位小数
                String actualTotal = totalDecimal3.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                //如果没有价格调整： 实收=应收
                if (!StringUtils.isEmpty(billing.getAdjustmentTypeCode()) && null != billing.getActualTotal()) {
                    actualTotal = billing.getActualTotal().setScale(2, BigDecimal.ROUND_HALF_UP).toString();
                }
                BillingListStringDto billingListStringDto = new BillingListStringDto();
                BeanUtils.copyProperties(billing, billingListStringDto);
                billingListStringDto.setTotal(total);
                billingListStringDto.setActualTotal(actualTotal);
                billingListStringDtos.add(billingListStringDto);
            });
        }
        return new QResultDto(billingListStringDtos, pd.getPagination());
    }

    /**
     * 账单列表-客户平台
     *
     * @param q
     * @param billNo
     * @param orderNo
     * @param customerId
     * @param status
     * @param includeAllAudit
     * @param startEffectTime
     * @param endEffectTime
     * @param pageIndex
     * @param pageSize
     * @param sort
     * @param asc
     * @return
     */
    @Override
    public QResultDto findByFilterForCustomer(String q, String billNo, String orderNo, Integer customerId, BillingStatus status, boolean includeAllAudit, String startEffectTime, String endEffectTime, int pageIndex, int pageSize, String sort, Boolean asc, String type) {
        //查询参数
        q = StringUtils.replaceSqlPattern(q);
        QueryParams params = new QueryParams(pageSize, pageIndex, q, sort, asc);
        params.put("startEffectTime", populateTime(startEffectTime));
        params.put("endEffectTime", populateTime(endEffectTime));
        params.put("billNo", billNo);
        params.put("type", type);
        params.put("orderNo", orderNo);
        params.put("status", status);
        params.put("includeAllAudit", includeAllAudit);
        params.put("taskStatus", BillingTaskStatus.COMPLETED);

        List<BillingListCustomerDto> billingList;
        ParamData pd = new ParamData();

        List<BillingListStringCustomerDto> billingListStringCustomerDtos = null;
        //帐号信息
        CusUser primaryAccount = isPrimaryAccount(customerId);
        if (null != primaryAccount) {
            if (!primaryAccount.getIsSystem()) {
                List<Integer> customerIds = new ArrayList<>();
                customerIds.add(primaryAccount.getId());
                params.put("userIds", customerIds);
                pd = convertQueryParams(params);
                billingList = dao.findForList(getSqlName("findByFilterListPageForCustomer"), pd, BillingListCustomerDto.class);
                //子帐号
                if (!primaryAccount.getIsSystem()) {
                    //计算子帐号下所有订单金额
                    if (null != billingList && billingList.size() > 0) {
                        billingListStringCustomerDtos = new ArrayList<>();
                        for (BillingListCustomerDto billing : billingList) {
                            Map<String, Object> amountSub = billingOrderRelService.getOrderAmountSub(billing.getBillNo(), customerId, type);
                            if (null != amountSub) {
                                billing.setStatusName(billing.getStatus().getName());
                                billing.setTotal(((BigDecimal) amountSub.get("total")).setScale(3, BigDecimal.ROUND_HALF_UP));
                                billing.setActualTotal(billing.getTotal().setScale(2, BigDecimal.ROUND_HALF_UP));
                                billing.setAdjustmentRemark(null);
                                billing.setAdjustment(null);
                                billing.setAdjustmentUnit(null);
                                billing.setAdjustmentTypeCode(null);
                                billing.setAdjustmentValue(null);
                                billing.setIsShowAdjustment(false);
                                populateList(billing, billingListStringCustomerDtos);
                            }
                        }
                    }
                }
            } else {  //主帐号
                List<Integer> customerIds = new ArrayList<>();
                //获取账户下所有帐号
                List<CusUser> cusUsers = customerUserService.findByCustomerIdIncludeDeleted(primaryAccount.getRefCustomerId());
                if (null != cusUsers && cusUsers.size() > 0) {
                    cusUsers.forEach(cusUser -> {
                        customerIds.add(cusUser.getId());
                    });
                }
                params.put("userIds", customerIds);
                pd = convertQueryParams(params);
                billingList = dao.findForList(getSqlName("findByFilterListPageForCustomer"), pd, BillingListCustomerDto.class);
                if (null != billingList && billingList.size() > 0) {
                    billingListStringCustomerDtos = new ArrayList<>();
                    for (BillingListCustomerDto billing : billingList) {
                        billing.setStatusName(billing.getStatus().getName());
                        billing.setAdjustment(adjustment(billing.getAdjustmentTypeCode(), billing.getAdjustmentValue(), billing.getAdjustmentUnit()));
                        billing.setIsShowAdjustment(true);
                        populateList(billing, billingListStringCustomerDtos);
                    }
                }
            }
        }
        return new QResultDto(billingListStringCustomerDtos, pd.getPagination());
    }

    /**
     * 数据交换账单接口
     *
     * @param billNo
     * @param orderNo
     * @param customerId
     * @param startEffectTime
     * @param endEffectTime
     * @return
     */
    @Override
    public List<BillingListStringCustomerDto> billsForEDI(String billNo, String orderNo, Integer customerId, Date startEffectTime, Date endEffectTime, String type) {
        ParamData paramData = new ParamData();
        paramData.put("billNo", billNo);
        paramData.put("orderNo", orderNo);
        paramData.put("customerId", customerId);
        paramData.put("startEffectTime", startEffectTime);
        paramData.put("endEffectTime", endEffectTime);
        paramData.put("taskStatus", BillingTaskStatus.COMPLETED);
        paramData.put("type", type);

        List<BillingListCustomerDto> billingList;

        List<BillingListStringCustomerDto> billingListStringCustomerDtos = null;
        //帐号信息
        CusUser primaryAccount = isPrimaryAccount(customerId);
        if (null != primaryAccount) {
            if (!primaryAccount.getIsSystem()) {
                List<Integer> customerIds = new ArrayList<>();
                customerIds.add(primaryAccount.getId());
                paramData.put("userIds", customerIds);
                billingList = dao.findForList(getSqlName("billsForEDI"), paramData, BillingListCustomerDto.class);
                //子帐号
                if (!primaryAccount.getIsSystem()) {
                    //计算子帐号下所有订单金额
                    if (null != billingList && billingList.size() > 0) {
                        billingListStringCustomerDtos = new ArrayList<>();
                        for (BillingListCustomerDto billing : billingList) {
                            Map<String, Object> amountSub = billingOrderRelService.getOrderAmountSub(billing.getBillNo(), customerId, type);
                            if (null != amountSub) {
                                billing.setStatusName(billing.getStatus().getName());
                                billing.setTotal(((BigDecimal) amountSub.get("total")).setScale(3, BigDecimal.ROUND_HALF_UP));
                                billing.setActualTotal(billing.getTotal().setScale(2, BigDecimal.ROUND_HALF_UP));
                                billing.setAdjustmentRemark(null);
                                billing.setAdjustment(null);
                                billing.setAdjustmentUnit(null);
                                billing.setAdjustmentTypeCode(null);
                                billing.setAdjustmentValue(null);
                                populateList(billing, billingListStringCustomerDtos);
                            }
                        }
                    }
                }
            } else {  //主帐号
                List<Integer> customerIds = new ArrayList<>();
                //获取账户下所有帐号
                List<CusUser> cusUsers = customerUserService.findByCustomerIdIncludeDeleted(primaryAccount.getRefCustomerId());
                if (null != cusUsers && cusUsers.size() > 0) {
                    cusUsers.forEach(cusUser -> {
                        customerIds.add(cusUser.getId());
                    });
                }
                paramData.put("userIds", customerIds);
                billingList = dao.findForList(getSqlName("billsForEDI"), paramData, BillingListCustomerDto.class);
                if (null != billingList && billingList.size() > 0) {
                    billingListStringCustomerDtos = new ArrayList<>();
                    for (BillingListCustomerDto billing : billingList) {
                        billing.setStatusName(billing.getStatus().getName());
                        billing.setAdjustment(adjustment(billing.getAdjustmentTypeCode(), billing.getAdjustmentValue(), billing.getAdjustmentUnit()));
                        populateList(billing, billingListStringCustomerDtos);
                    }
                }
            }
        }
        return billingListStringCustomerDtos;
    }

    /**
     * 组装返回数据
     *
     * @param billing
     * @param billingListCustomerDtos
     * @return
     */
    private List<BillingListStringCustomerDto> populateList(BillingListCustomerDto billing, List<BillingListStringCustomerDto> billingListCustomerDtos) {
        //应收保留三位小数
        BigDecimal totalDecimal3 = billing.getTotal().setScale(3, BigDecimal.ROUND_HALF_UP);
        String total = billing.getTotal().setScale(3, BigDecimal.ROUND_HALF_UP).toString();
        //实收保留两位小数
        String actualTotal = totalDecimal3.setScale(2, BigDecimal.ROUND_HALF_UP).toString();
        //如果没有价格调整： 实收=应收
        if (!StringUtils.isEmpty(billing.getAdjustmentTypeCode()) && null != billing.getActualTotal()) {
            actualTotal = billing.getActualTotal().setScale(2, BigDecimal.ROUND_HALF_UP).toString();
        }
        BillingListStringCustomerDto billingListStringCustomerDto = new BillingListStringCustomerDto();
        BeanUtils.copyProperties(billing, billingListStringCustomerDto);
        billingListStringCustomerDto.setTotal(total);
        billingListStringCustomerDto.setActualTotal(actualTotal);
        billingListCustomerDtos.add(billingListStringCustomerDto);
        return billingListCustomerDtos;
    }

    /**
     * 是否主帐号
     *
     * @param customerId
     * @return
     */
    private CusUser isPrimaryAccount(Integer customerId) {
        CusUser user = customerUserService.findById(customerId);
        return user;
    }

    /**
     * 优惠价格
     *
     * @param adjustmentTypeCode
     * @param adjustmentValue
     * @param adjustmentUnit
     * @return
     */
    private String adjustment(BillingAdjustmentTypeCode adjustmentTypeCode, BigDecimal adjustmentValue, String adjustmentUnit) {
        String result = "0";
        adjustmentValue = adjustmentValue == null ? BigDecimal.ZERO : adjustmentValue;
        String adjustmentUnitPS = "";
        String adjustmentType = "";

        // 上调
        if (null != adjustmentTypeCode && adjustmentTypeCode.getCode().equals(BillingAdjustmentTypeCode.UPPER.getCode())) {
            adjustmentType = "+";
        } else if (null != adjustmentTypeCode && adjustmentTypeCode.getCode().equals(BillingAdjustmentTypeCode.DOWN.getCode())) { // 下调
            adjustmentType = "-";
        }
        // adjustmentUnit: 1 : %; 2: 金额
        if (null != adjustmentUnit && QuotationConstants.DiscountType.PERCENTAGE.equals(adjustmentUnit)) {
            adjustmentUnitPS = "%";
        }
        result = adjustmentType + adjustmentValue + adjustmentUnitPS;
        return result;
    }

    /**
     * 转换时间
     *
     * @param timeString
     * @return
     */
    @Override
    public Date populateTime(String timeString) {
        Date date = null;
        try {
            if (!StringUtils.isEmpty(timeString)) {
                date = DateUtils.convertToDate(timeString, DateUtils.FORMAT_DETAIL);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return date;
    }

    /**
     * 根据账单号获取账单信息
     *
     * @param billNo
     * @return
     */
    @Override
    public Billing findByBillNo(String billNo, String type) {
        ParamData paramData = new ParamData();
        paramData.put("billNo", billNo);
        paramData.put("type", type);
        return (Billing) dao.findForObject(getSqlName("findByBillNo"), paramData);
    }

    /**
     * 所有价格调整类型
     *
     * @return
     */
    @Override
    public List<CommonModel> getAdjustmentType() {
        List<CommonModel> list = Lists.newArrayList();
        for (BillingAdjustmentTypeCode billingAdjustmentTypeCode : BillingAdjustmentTypeCode.values()) {
            CommonModel dto = new CommonModel(billingAdjustmentTypeCode.name(), billingAdjustmentTypeCode.getName());
            list.add(dto);
        }
        return list;
    }

    /**
     * 所有账单状态-客户平台
     *
     * @return
     */
    @Override
    public List<CommonModel> getBillingStatusForCustomer() {
        List<CommonModel> list = Lists.newArrayList();
        for (BillingStatus billingStatus : BillingStatus.values()) {
            if (billingStatus.name().equals(BillingStatus.CHARGEOFF.name()) || billingStatus.name().equals(BillingStatus.PAID.name()) || billingStatus.name().equals(BillingStatus.OVERDUE.name())) {
                CommonModel dto = new CommonModel(billingStatus.name(), billingStatus.getName());
                list.add(dto);
            }
        }
        return list;
    }

    /**
     * 所有账单状态
     *
     * @param isAudit
     * @return
     */
    @Override
    public List<CommonModel> getBillingStatus(String isAudit) {
        List<CommonModel> list = Lists.newArrayList();
        for (BillingStatus billingStatus : BillingStatus.values()) {
            if (!StringUtils.isEmpty(isAudit) && "1".equals(isAudit)) {
                if (billingStatus.name().equals(BillingStatus.AUDITING.name())
                        || billingStatus.name().equals(BillingStatus.CHARGEOFF.name())
                        || billingStatus.name().equals(BillingStatus.PAID.name())
                        || billingStatus.name().equals(BillingStatus.OVERDUE.name())) {
                    CommonModel dto = new CommonModel(billingStatus.name(), billingStatus.getName());
                    list.add(dto);
                }
            } else {
                if (billingStatus.name().equals(BillingStatus.DRAFT.name())
                        || billingStatus.name().equals(BillingStatus.AUDITING.name())
                        || billingStatus.name().equals(BillingStatus.CHARGEOFF.name())
                        || billingStatus.name().equals(BillingStatus.PAID.name())
                        || billingStatus.name().equals(BillingStatus.OVERDUE.name())) {
                    CommonModel dto = new CommonModel(billingStatus.name(), billingStatus.getName());
                    list.add(dto);
                }
            }
        }
        return list;
    }

    /**
     * 组装账单信息
     *
     * @param customerId
     * @param startEffectTime
     * @param endEffectTime
     * @param taskCode
     * @param ordServiceBillList
     * @return
     */
    private Billing populateBilling(Integer customerId, Date startEffectTime, Date endEffectTime, String taskCode, List<BillingGenerateDto> ordServiceBillList, List<BillingGenerateDto> ordServiceBillListGenerated, String type) {
        // 计算订单金额综总和
        BigDecimal total = null;
        // 获取货币类型
        String currencyType = null;
        if (null != ordServiceBillList && ordServiceBillList.size() > 0) {
            currencyType = ordServiceBillList.get(0).getCurrencyType();
        }
        // 组装账单信息
        String uid = generateNewUid();
        Billing billing = new Billing();
        billing.setBillNo(uid);
        billing.setCustomerId(customerId);
        billing.setStatus(BillingStatus.DRAFT);
        billing.setCurrencyType(currencyType);
        billing.setType(type);
        if (null != ordServiceBillList) {
            total = countBillingMoney(ordServiceBillList);
            billing.setOrderQuantity(ordServiceBillList.size());
            if (null != ordServiceBillListGenerated && ordServiceBillListGenerated.size() > 0) {
                billing.setOrderQuantity(ordServiceBillList.size() + ordServiceBillListGenerated.size());
            }
        }
        //设置应收款四舍五入
        if (null != total) {
            total = total.setScale(3, BigDecimal.ROUND_HALF_UP);
        }
        billing.setTotal(total);
        billing.setIsActive(true);
        if (StringUtils.isEmpty(taskCode)) {
            billing.setTaskCode(generateNewUid());
        } else {
            billing.setTaskCode(taskCode);
        }
        billing.setStartEffectTime(startEffectTime);
        billing.setEndEffectTime(endEffectTime);

        return billing;
    }

    /**
     * 账单应收款，实收款
     *
     * @param ordServiceBillList
     * @return
     */
    private BigDecimal countBillingMoney(List<BillingGenerateDto> ordServiceBillList) {
        BigDecimal total = new BigDecimal(0);
        for (BillingGenerateDto billingGenerateDto : ordServiceBillList) {
            if (null != billingGenerateDto.getTotal()) {
                total = total.add(billingGenerateDto.getTotal());
            }
        }
        return total;
    }

    /**
     * 获取时间段内所有订单信息
     *
     * @param billingTaskDto
     * @param isBilled
     * @return
     */
    @Override
    public List<BillingGenerateDto> getOrderByCustomerIdAndCreateTime(BillingTaskDto billingTaskDto, boolean isBilled) {
        List<BillingGenerateDto> ordServiceBillList = null;
        try {
            Integer customerId = billingTaskDto.getCustomerId();
            if (TrdConstants.BillType.PAYABLE.equals(billingTaskDto.getType())) {
                ordServiceBillList = ordServiceBillService.getOrderByCustomerAndCreateTime(customerId, billingTaskDto.getStartEffectTime(), billingTaskDto.getEndEffectTime(), isBilled);
            } else {
                ordServiceBillList = ordServiceBillService.getOrderBySupplierAndCreateTime(customerId, billingTaskDto.getStartEffectTime(), billingTaskDto.getEndEffectTime(), isBilled);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return ordServiceBillList;
    }

    /**
     * 验证时间段是否正确
     *
     * @param billingTaskDto
     */
    private void validateStartEndTime(BillingTaskDto billingTaskDto) {
        // 判断查询订单的时间段参数是否存正确
        if (null == billingTaskDto || StringUtils.isEmpty(billingTaskDto.getStartEffectTime())
                || StringUtils.isEmpty(billingTaskDto.getEndEffectTime())) {
            throw Exceptions.bizException(ErrorCodes.ERROR_ARGUMENT_ILLEGAL);
        }
    }

    @Override
    protected String getMapperNamespace() {
        return "com.sz.biz.logistics.bill.entity.BillingMapper";
    }
}
